from DAL.sqlHelper import (get_adm_conn as conn_a,SQL_ADD_FIELD_VALUES
,SQL_UPDATE_SET,SQL_ORDER,SQL_WHERE,SQL_LIKE)
import xlrd
"""
行政管理模块，包括手机卡管理、设备管理、操作日志管理
"""

class logDAL:
    def getList(self,start=None,end=None,name="",content=None,sql_record=None,page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
            SELECT id,`name`,`content`,`sql_record`,DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS create_time
            FROM `adm_log`     
               {0}
               {1}
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param,only_contain_field= ['name','content','sql_record'])
        sql_where = sql_where.format(sql_3param)
        if start is not None:
            sql_where+=f" and create_time>='{start}' and create_time<ADDDATE('{end}', 1) "

        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `adm_log`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def add(self, name='',content='',sql_record=''):
        param = locals()
        param['name'] = param['name'].replace("'","''")
        param['content'] = param['content'].replace("'", "''")
        param['sql_record'] = param['sql_record'].replace("'", "''")
        sql_field,sql_values=SQL_ADD_FIELD_VALUES(param)
        sql = """
               INSERT INTO  `adm_log`({0},create_time)
               VALUES({1},NOW())
               """
        sql = sql.format(sql_field,sql_values)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()


class cartDAL:
    def getList(self,start=None,end=None, phone_num="",belong_to=None,tel_traffic=None,tel_type=None,page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
               SELECT id,phone_num,belong_to,tel_package,
                tel_traffic,tel_type,DATE_FORMAT(discount_exp_date, '%Y-%m-%d') AS discount_exp_date,wechat_pwd 
               FROM `adm_phonecard`       
               {0}
               {1}
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param, only_contain_field=['phone_num', 'belong_to', 'tel_traffic'])
        sql_where = sql_where.format(sql_3param)
        if tel_type:
            sql_where+=f" and tel_type='{tel_type}' "
        if start is not None:
            sql_where += f" and discount_exp_date>='{start}' and discount_exp_date<ADDDATE('{end}', 1) "

        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `adm_phonecard`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  adm_phonecard
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,phone_num=None,belong_to=None, tel_package=None,tel_traffic=None
                    ,tel_type=None,discount_exp_date=None, wechat_pwd=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `adm_phonecard`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def isExist_num(self,phone_num=''):
        sql_1 = """
            SELECT id
            FROM `adm_phonecard`
            WHERE phone_num='{0}'
            """
        sql_1 = sql_1.format(phone_num)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.fetchone()
                return res
        finally:
            conn.close()

    def getOneByNum(self,phone_num=''):
        sql_1 = """
            SELECT id,phone_num,belong_to,tel_package,
                tel_traffic,tel_type,DATE_FORMAT(discount_exp_date, '%Y-%m-%d') AS discount_exp_date,wechat_pwd 
            FROM `adm_phonecard`
            WHERE phone_num='{0}'
            """
        sql_1 = sql_1.format(phone_num)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.fetchone()
                return res
        finally:
            conn.close()


    def notExistAdd(self, phone_num=None,belong_to=None, tel_package=None,tel_traffic=None
                    ,tel_type=None,discount_exp_date=None, wechat_pwd=None):
        param = locals()
        sql = """
               INSERT INTO  `adm_phonecard`({0})
               VALUES({1})
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `adm_phonecard`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param,only_contain_field=['phone_num'])
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count'] > 0:
                    res = 0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()


    def batch_processing(self, file_url=""):
        conn = conn_a()
        def open_excel():
            try:
                book = xlrd.open_workbook(file_url)
                sheet = book.sheet_by_name("手机卡")
                # print(sheet)
                return sheet
            except:
                print("上传的excel有问题")
                return None

        def insert_deta():
            cursor = conn.cursor()
            ret = {
                "success": 0,
                "fail": 0,
                "repeat": 0
            }
            sheet = open_excel()
            for i in range(1, sheet.nrows):
                param = {
                    'phone_num':str(sheet.cell(i, 0).value),
                    'belong_to': str(sheet.cell(i, 1).value),
                    'tel_package': str(sheet.cell(i, 2).value),
                    'tel_traffic': str(sheet.cell(i, 3).value),
                    'tel_type': str(sheet.cell(i, 4).value),
                    'discount_exp_date': str(sheet.cell(i, 5).value),
                    'wechat_pwd': str(sheet.cell(i, 6).value)
                }
                sql = """
                               INSERT INTO  `adm_phonecard`({0})
                               VALUES({1})
                      """
                sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
                sql = sql.format(sql_field, sql_values)
                sql_1 = """
                       SELECT count(id) AS count
                       FROM `adm_phonecard`
                       WHERE {0}      
                       """
                sql_where = SQL_WHERE(param, only_contain_field=['phone_num'])
                sql_1 = sql_1.format(sql_where)
                try:
                    cursor.execute(sql_1)
                    conn.commit()
                    res_one = cursor.fetchone()
                    # print(res_one)
                    if res_one['count'] > 0:
                        ret['repeat'] += 1
                    else:
                        cursor.execute(sql)  # 执行sql语句
                        conn.commit()
                        if cursor.rowcount>0:
                            ret['success']+=1
                        else:
                            ret['fail'] += 1
                except:
                    ret['fail'] += 1
            return ret
        return insert_deta()
        pass



class deviceDAL:
    def getList(self, asset_type="",device_no=None,brand_model=None,worth=None,
                user=None,remark=None,
                page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
               SELECT id,asset_type,device_no,brand_model
                ,worth,`user`,remark
                FROM `adm_device`     
               {0}
               {1}
              """
        sql_where = SQL_LIKE(locals_filed=param
                             ,only_contain_field=['asset_type','device_no','brand_model','user','remark'])
        sql_where = "  WHERE   {0}  ".format(sql_where)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `adm_device`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()


    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  adm_device
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,asset_type=None,device_no=None,brand_model=None,
        worth=None,user=None,remark=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `adm_device`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self, asset_type=None,device_no=None,brand_model=None,
        worth=None,user=None,remark=None):
        param = locals()
        sql = """
               INSERT INTO  `adm_device`({0})
               VALUES({1})
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `adm_device`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param,only_contain_field=["device_no"])
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()


    def batch_processing(self, file_url=""):
        conn = conn_a()
        def open_excel():
            try:
                book = xlrd.open_workbook(file_url)  # 文件名，把文件与py文件放在同一目录下
                sheet = book.sheet_by_name("设备信息表")  # execl里面的worksheet1
                # print(sheet)
                return sheet
            except:
                print("上传的excel有问题")
                return None

        def insert_deta():
            cursor = conn.cursor()
            ret = {
                "success": 0,
                "fail": 0,
                "repeat": 0
            }
            sheet = open_excel()
            for i in range(1, sheet.nrows):
                param = {
                    'asset_type':str(sheet.cell(i, 0).value),
                    'device_no': str(sheet.cell(i, 1).value),
                    'brand_model': str(sheet.cell(i, 2).value),
                    'worth': str(sheet.cell(i, 3).value),
                    'user': str(sheet.cell(i, 4).value),
                    'remark': str(sheet.cell(i, 5).value)
                }
                sql = """
                               INSERT INTO  `adm_device`({0})
                               VALUES({1})
                               """
                sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
                sql = sql.format(sql_field, sql_values)

                sql_1 = """
                       SELECT count(id) AS count
                       FROM `adm_device`
                       WHERE {0}      
                       """
                sql_where = SQL_WHERE(param, only_contain_field=["device_no"])
                sql_1 = sql_1.format(sql_where)
                try:
                    cursor.execute(sql_1)
                    conn.commit()
                    res_one = cursor.fetchone()
                    # print(res_one)
                    if res_one['count'] > 0:
                        ret['repeat'] += 1
                    else:
                        cursor.execute(sql)  # 执行sql语句
                        conn.commit()
                        if cursor.rowcount>0:
                            ret['success']+=1
                        else:
                            ret['fail'] += 1
                except:
                    ret['fail'] += 1
            return ret

        try:
            res = insert_deta()
        finally:
            conn.close()
        return res
        pass



class mbdeviceDAL:
    def getList(self, asset_type="",device_no=None,brand_model=None,worth=None,
                user=None,card_1=None,card_2=None,
                page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
               SELECT id,asset_type,device_no,brand_model
                ,worth,`user`,card_1,card_2
                FROM `adm_mbdevice`     
               {0}
               {1}
              """
        sql_where = SQL_LIKE(locals_filed=param
                             ,only_contain_field=['asset_type','device_no','brand_model','user','card_1','card_2'])
        sql_where = "  WHERE   {0}  ".format(sql_where)
        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `adm_mbdevice`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()


    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  adm_mbdevice
                WHERE id='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def update(self, id=None,asset_type=None,device_no=None,brand_model=None,
        worth=None,user=None,card_1=None,card_2=None):
        pram = locals()
        sql_set = SQL_UPDATE_SET(pram, ['id'])
        sql = """
                 UPDATE 
                   `adm_mbdevice`
                   SET  {1}
                   WHERE id = '{0}'
                 """
        sql = sql.format(id, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self, asset_type=None,device_no=None,brand_model=None,
        worth=None,user=None,card_1=None,card_2=None):
        param = locals()
        sql = """
               INSERT INTO  `adm_mbdevice`({0})
               VALUES({1})
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `adm_mbdevice`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(param,only_contain_field=["device_no"])
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()


    def batch_processing(self, file_url=""):
        conn = conn_a()
        def open_excel():
            try:
                book = xlrd.open_workbook(file_url)  # 文件名，把文件与py文件放在同一目录下
                sheet = book.sheet_by_name("设备信息表")  # execl里面的worksheet1
                # print(sheet)
                return sheet
            except:
                print("上传的excel有问题")
                return None

        def insert_deta():
            cursor = conn.cursor()
            ret = {
                "success": 0,
                "fail": 0,
                "repeat": 0
            }
            sheet = open_excel()
            for i in range(1, sheet.nrows):
                param = {
                    'asset_type':str(sheet.cell(i, 0).value),
                    'device_no': str(sheet.cell(i, 1).value),
                    'brand_model': str(sheet.cell(i, 2).value),
                    'worth': str(sheet.cell(i, 3).value),
                    'user': str(sheet.cell(i, 4).value),
                    'card_1': str(sheet.cell(i, 5).value),
                    'card_2': str(sheet.cell(i, 6).value)
                }
                sql = """
                               INSERT INTO  `adm_mbdevice`({0})
                               VALUES({1})
                               """
                sql_field, sql_values = SQL_ADD_FIELD_VALUES(param)
                sql = sql.format(sql_field, sql_values)

                sql_1 = """
                       SELECT count(id) AS count
                       FROM `adm_mbdevice`
                       WHERE {0}      
                       """
                sql_where = SQL_WHERE(param, only_contain_field=["device_no"])
                sql_1 = sql_1.format(sql_where)
                try:
                    cursor.execute(sql_1)
                    conn.commit()
                    res_one = cursor.fetchone()
                    # print(res_one)
                    if res_one['count'] > 0:
                        ret['repeat'] += 1
                    else:
                        cursor.execute(sql)  # 执行sql语句
                        conn.commit()
                        if cursor.rowcount>0:
                            ret['success']+=1
                        else:
                            ret['fail'] += 1
                except:
                    ret['fail'] += 1
            return ret
        try:
            res = insert_deta()
        finally:
            conn.close()
        return res
        pass




if __name__=="__main__":
    lDal = cartDAL()
    res = lDal.batch_processing(file_url="C:/Users/Administrator/Desktop/code/yulonghuyu/ylhy_api/static/upload_file/3039cart_model.xls")

    print(res)












